package com.agilya.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import com.agilya.dao.daointerface.QCMDAO;
import com.agilya.dao.exception.DAOExceptions;
import com.agilya.enumere.TypeAcces;
import com.agilya.modele.Chapitre;
import com.agilya.modele.QCM;
import com.sun.appserv.jdbc.DataSource;

/**
 * 
 * DAO regroupant toutes les actions en relation avec le QCM
 * 
 * @author jb
 * 
 */
@DAOQCM
public class JDBCQCMDAO implements QCMDAO {

	@Resource(name = "jdbc/QCMAgilya")
	private DataSource ds;

	@Override
	public List<QCM> getAllQCM() throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}
		List<QCM> listeQCM = new ArrayList<>();

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM QCMAgilya.QCM");) {
			// psmt.setInt();
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {
				// REF_SACHANT
				int id = rs.getInt("ID_QCM");
				String libelle = rs.getString("LIBELLE");
				Date dateCreation = rs.getDate("DATE_CREATION_QCM");
				int dureeValiditeJour = rs.getInt("DUREE_VALIDITE_JOUR_QCM");
				int dureeFaisabiliteMinute = rs.getInt("DUREE_FAISABILITE_MINUTE_QCM");
				boolean retourArriere = rs.getBoolean("RETOUR_ARRIERE");
				boolean correctionDisponible = rs.getBoolean("CORRECTION_DISPONIBLE");
				String theme = rs.getString("THEME");
				TypeAcces typeAcces = TypeAcces.valueOf(rs.getString("TYPE_ACCES"));
				double pointParBonneProposition = rs.getDouble("POINT_PAR_BONNE_PROPOSITION");
				double pointParMauvaiseProposition = rs.getDouble("POINT_PAR_MAUVAISE_PROPOSITION");
				double plafondErreur = rs.getDouble("PLAFOND_ERREUR");
				String pageAccueil = rs.getString("PAGE_ACCUEIL");
				String header = rs.getString("HEADER");
				String pageFin = rs.getString("PAGE_FIN");
				int nbQuestion = rs.getInt("NB_QUESTION");

				QCM qcm = new QCM(id, libelle, dateCreation, dureeValiditeJour, dureeFaisabiliteMinute, retourArriere, correctionDisponible, theme, typeAcces, pointParBonneProposition, pointParMauvaiseProposition, plafondErreur, pageAccueil, header, pageFin, nbQuestion);
				listeQCM.add(qcm);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listeQCM;
	}

	@Override
	public List<QCM> getAllQCMbyChapitre(int idChapitre) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		List<QCM> listeqcm = new ArrayList<>();

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement(" SELECT * FROM QCM q JOIN QCM_NN_CHAPITRE qnc ON (q.ID_QCM=qnc.REF_QCM) WHERE REF_CHAPITRE = ?");) {
			psmt.setInt(1, idChapitre);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("ID_QCM");
				String libelle = rs.getString("LIBELLE");
				Date dateCreation = rs.getDate("DATE_CREATION_QCM");
				int dureeValidite = rs.getInt("DUREE_VALIDITE_JOUR_QCM");
				int dureeFaisabilite = rs.getInt("DUREE_FAISABILITE_MINUTE_QCM");
				boolean retourArriere = rs.getBoolean("RETOUR_ARRIERE");
				boolean correctionDisponible = rs.getBoolean("CORRECTION_DISPONIBLE");
				String theme = rs.getString("THEME");
				TypeAcces typeAcces = TypeAcces.valueOf(rs.getString("TYPE_ACCES"));
				double pointParBonneProposition = rs.getDouble("POINT_PAR_BONNE_PROPOSITION");
				double pointParMauvaiseProposition = rs.getDouble("POINT_PAR_MAUVAISE_PROPOSITION");
				double plafondErreur = rs.getDouble("PLAFOND_ERREUR");
				String pageAccueil = rs.getString("PAGE_ACCUEIL");
				String header = rs.getString("HEADER");
				String pageFin = rs.getString("PAGE_FIN");
				int nbQuestion = rs.getInt("NB_QUESTION");

				QCM qcm = new QCM(id, libelle, dateCreation, dureeValidite, dureeFaisabilite, retourArriere, correctionDisponible, theme, typeAcces, pointParBonneProposition, pointParMauvaiseProposition, plafondErreur, pageAccueil, header, pageFin, nbQuestion);
				listeqcm.add(qcm);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listeqcm;

	}

	// Requete qui permet d'afficher tous les QCM d'un chapitre
	// SELECT libelle FROM QCM q JOIN CHAPITRE_NN_QCM cnq ON (q.ID_QCM=cnq.REF_QCM) WHERE REF_CHAPITRE = 1

	@Override
	public QCM getQCMbyId(int idqcm) throws DAOExceptions {
		// TODO Auto-generated method stub

		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		QCM qcm = null;

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM QCM WHERE ID_QCM = ?");) {
			psmt.setInt(1, idqcm);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				String libelle = rs.getString("LIBELLE");
				Date dateCreation = rs.getDate("DATE_CREATION_QCM");
				int dureeValiditeJour = rs.getInt("DUREE_VALIDITE_JOUR_QCM");
				int dureeFaisabiliteMinute = rs.getInt("DUREE_FAISABILITE_MINUTE_QCM");
				boolean retourArriere = rs.getBoolean("RETOUR_ARRIERE");
				boolean correctionDisponible = rs.getBoolean("CORRECTION_DISPONIBLE");
				String theme = rs.getString("THEME");
				TypeAcces typeAcces = TypeAcces.valueOf(rs.getString("TYPE_ACCES"));
				double pointParBonneProposition = rs.getDouble("POINT_PAR_BONNE_PROPOSITION");
				double pointParMauvaiseProposition = rs.getDouble("POINT_PAR_MAUVAISE_PROPOSITION");
				double plafondErreur = rs.getDouble("PLAFOND_ERREUR");
				String pageAccueil = rs.getString("PAGE_ACCUEIL");
				String header = rs.getString("HEADER");
				String pageFin = rs.getString("PAGE_FIN");
				int nbQuestion = rs.getInt("NB_QUESTION");

				qcm = new QCM(idqcm, libelle, dateCreation, dureeValiditeJour, dureeFaisabiliteMinute, retourArriere, correctionDisponible, theme, typeAcces, pointParBonneProposition, pointParMauvaiseProposition, plafondErreur, pageAccueil, header, pageFin, nbQuestion);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return qcm;
	}

	@Override
	public Integer enregistrerQCM(QCM qcm, List<Chapitre> chapitres) throws DAOExceptions {

		Integer idQCM = null;

		String requeteInsertQCM = "INSERT INTO QCM(" + "LIBELLE, DATE_CREATION_QCM, DUREE_VALIDITE_JOUR_QCM, " + "DUREE_FAISABILITE_MINUTE_QCM, RETOUR_ARRIERE, CORRECTION_DISPONIBLE, " + "THEME, TYPE_ACCES, POINT_PAR_BONNE_PROPOSITION, " + "POINT_PAR_MAUVAISE_PROPOSITION, PLAFOND_ERREUR, "
				+ "PAGE_ACCUEIL, HEADER, PAGE_FIN, REF_SACHANT) " + "VALUES (?,?,?," + "?,?,?,?,?," + "?,?,?,?,?," + "?,?)";

		String requeteInsertQCMChapitre = "INSERT INTO QCM_NN_CHAPITRE(REF_QCM, REF_CHAPITRE) VALUES (?,?)";

		if (ds == null) {
			// Si il n'y a pas de Datasource, inutile d'aller plus loin
			throw new DAOExceptions("Base de données non accessible");
		}
		try (Connection c = ds.getConnection(); PreparedStatement pstm = c.prepareStatement(requeteInsertQCM, Statement.RETURN_GENERATED_KEYS)) {

			pstm.setString(1, qcm.getLibelle());
			pstm.setDate(2, new java.sql.Date(qcm.getDateCreation().getTime()));
			pstm.setInt(3, qcm.getDureeValiditeJour());
			pstm.setInt(4, qcm.getDureeFaisabiliteMinute());
			pstm.setBoolean(5, qcm.isRetourArriere());
			pstm.setBoolean(6, qcm.isCorrectionDisponible());
			pstm.setString(7, qcm.getTheme());
			pstm.setString(8, qcm.getTypeAcces().name());
			pstm.setDouble(9, qcm.getPointParBonneProposition());
			pstm.setDouble(10, qcm.getPointParMauvaiseProposition());
			pstm.setDouble(11, qcm.getPlafondErreur());
			pstm.setString(12, qcm.getPageAccueil());
			pstm.setString(13, qcm.getHeader());
			pstm.setString(14, qcm.getPageFin());
			pstm.setInt(15, 1); // TODO (REF_SACHANT)

			idQCM = pstm.executeUpdate();

			// Récupération de l'identifiant
			ResultSet rs = pstm.getGeneratedKeys();

			if (rs.next()) {
				idQCM = (int) rs.getLong(1);
			}

			if (chapitres != null) {
				for (Chapitre chapitre : chapitres) {
					PreparedStatement psmt = c.prepareStatement(requeteInsertQCMChapitre);

					psmt.setInt(1, idQCM);
					psmt.setInt(2, chapitre.getId());

					psmt.executeUpdate();
				}
			}
		} catch (SQLException e) {

			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);

		}
		return idQCM;
	}

	@Override
	public void affecterQuestionsQCM(int idQCM, List<Integer> listeIDQuestion) throws DAOExceptions {
		String requeteInsert = "INSERT INTO QUESTION_NN_QCM (REF_QCM, REF_QUESTION, ORDRE) " + "VALUES(?,?,?)";
		String requeteCountProposition = "SELECT count( REF_QCM ) FROM QUESTION_NN_QCM WHERE REF_QCM =? GROUP BY REF_QCM";

		if (ds == null) {
			// Si il n'y a pas de Datasource, inutile d'aller plus loin
			throw new DAOExceptions("Base de données non accessible");
		}
		try (Connection c = ds.getConnection(); PreparedStatement pstmInsert = c.prepareStatement(requeteInsert); PreparedStatement pstmCountNbQuestion = c.prepareStatement(requeteCountProposition);) {

			if (listeIDQuestion != null) {
				for (int idQuestion : listeIDQuestion) {

					// on recherche le nombre de question déjà présente dans le qcm
					pstmCountNbQuestion.setInt(1, idQCM);
					ResultSet rsCount = pstmCountNbQuestion.executeQuery();

					int ordre = 0;
					while (rsCount.next()) {
						ordre = rsCount.getInt(1);
					}

					pstmInsert.setInt(1, idQCM);
					pstmInsert.setInt(2, idQuestion);
					pstmInsert.setInt(3, ordre);
					pstmInsert.executeUpdate();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);

		}

	}

	@Override
	public void desaffecterQuestionsQCM(int idQCM, List<Integer> listeIDQuestion) throws DAOExceptions {
		String requeteInsert = "DELETE FROM QUESTION_NN_QCM WHERE REF_QCM = ? AND REF_QUESTION = ?";

		if (ds == null) {
			// Si il n'y a pas de Datasource, inutile d'aller plus loin
			throw new DAOExceptions("Base de données non accessible");
		}
		try (Connection c = ds.getConnection(); PreparedStatement pstm = c.prepareStatement(requeteInsert)) {
			if (listeIDQuestion != null) {
				for (int idQuestion : listeIDQuestion) {
					pstm.setInt(1, idQCM);
					pstm.setInt(2, idQuestion);
					pstm.executeUpdate();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);

		}

	}
}
